Table of Contents¶

# must go first 
%matplotlib inline 
%config InlineBackend.figure_format='retina'

# plotting
import matplotlib as mpl
from matplotlib import pyplot as plt
import seaborn as sns
sns.set_context("poster", font_scale=1.3)
import folium

# system packages 
import os, sys
import warnings
warnings.filterwarnings('ignore')

# basic wrangling 
import numpy as np
import pandas as pd

# eda tools 
import pivottablejs
import missingno as msno
import pandas_profiling

# File with functions from prior notebook(s)
sys.path.append('../scripts/')
from aqua_helper import time_slice, country_slice, time_series, simple_regions, subregion, variable_slice

# Update matplotlib defaults to something nicer 
mpl_update = {'font.size':16,
              'xtick.labelsize':14,
              'ytick.labelsize':14,
              'figure.figsize':[12.0,8.0],
              'axes.color_cycle':['#0055A7', '#2C3E4F', '#26C5ED', '#00cc66', '#D34100', '#FF9700','#091D32'], 
              'axes.labelsize':20,
              'axes.labelcolor':'#677385',
              'axes.titlesize':20,
              'lines.color':'#0055A7',
              'lines.linewidth':3,
              'text.color':'#677385'}
mpl.rcParams.update(mpl_update)

Our plan¶

Crisp-DM Exploratory data analysis consists of the following major tasks, which we present linearly here because each task doesn't make much sense to do without the ones prior to it. However, in reality, you are going to constantly jump around from step to step. You may want to do all the steps for a subset of the variables first. Or often, an observation will bring up a question you want to investigate and you'll branch off and explore to answer that question before returning down the main path of exhaustive EDA.

Research the fields of the dataset
Form hypotheses/develop investigation themes to explore
Wrangle data
Assess quality of data
Profile data
Explore each individual variable in the dataset
Assess the relationship between each variable and the target
Assess interactions between variables
Explore data across many dimensions

Throughout the entire analysis you want to:

Capture a list of hypotheses and questions that come up for further exploration.
Record things to watch out for/ be aware of in future analyses.
Show intermediate results to colleagues to get a fresh perspective, feedback, domain knowledge. Don't do EDA in a bubble! Get feedback throughout especially from people removed from the problem and/or with relevant domain knowledge.
Position visuals and results together. EDA relies on your natural pattern recognition abilities so maximize what you'll find by putting visualizations and results in close proximity.

Write questions that results raise as you go. Keep updating list of hypotheses

Data wrangling¶

http://www.fao.org/nr/water/aquastat/data/query/index.html

data = pd.read_csv('../data/aquastat/aquastat.csv.gzip', compression='gzip')

data[['variable','variable_full']].drop_duplicates()

Simplify regions

data.region = data.region.apply(lambda x: simple_regions[x])

Data quality assessment and profiling¶

Before trying to understand what information is in the data, make sure you understand what the data represents and what's missing.

Overview¶

Basic things to do¶

Categorical: count, count distinct, assess unique values
Numerical: count, min, max
Spot-check random samples and samples that you are familiar with
Slice and dice

Main questions¶

What data isn't there?
Is the data that is there right?
Is the data being generated the way you think?

Helpful packages¶

Example backlog¶

Assess the prevalence of missing data across all data fields, assess whether its missing is random or systematic, and identify patterns when such data is missing
Identify any default values that imply missing data for a given field
Determine sampling strategy for quality assessment and initial EDA
For datetime data types, ensure consistent formatting and granularity of data, and perform sanity checks on all dates present in the data.
In cases where multiple fields capture the same or similar information, understand the relationships between them and assess the most effective field to use
Assess data type of each field
For discrete value types, ensure data formats are consistent
For discrete value types, assess number of distinct values and percent unique and do sanity check on types of answers
For continuous data types, assess descriptive statistics and perform sanity check on values
Understand relationships between timestamps and assess which to use in analysis
Slice data by device type, operating system, software version and ensure consistency in data across slices
For device or app data, identify version release dates and assess data for any changes in format or value around those dates

Missing data¶

What data isn’t there?

Questions to be considering

Are there systematic reasons for missing data?
Are there fields that are always missing at the same time ?
Is there information in what data is missing?

Package that provides a number of functions for visualizing what data is missing within a dataset: missingno

By variable¶

recent = time_slice(data, '2013-2017')

msno.bar(recent, labels=True)

Discussion: What questions does this figure bring up?

Add these to your list of questions!

msno.matrix(recent, labels=True)

Discussion: What additional information does this provide or what additional questions does it suggest?

Deep dive: exploitable variables¶

"Exploitable" variables are missing for most countries.

Question to consider: Does this happen in each time period?

msno.matrix(variable_slice(data, 'exploitable_total'), inline=False, sort='descending');
plt.xlabel('Time period');
plt.ylabel('Country');
plt.title('Missing total exploitable water resources data across countries and time periods \n \n \n \n');

Total exploitable water resources is only reported on for a fraction of the countries and only a very small fraction of those countries have data for the most recent time period. Either a) data has not been reported yet and it will be at some point or b) most countries have stopped reporting on this factor or c) we do not have the domain knowledge to understand what's happening.

We are going to remove exploitable variables for future analysis because such few data points can cause a lot of problems.

data = data.loc[~data.variable.str.contains('exploitable'),:]

Deep dive: National rainfall index¶

msno.matrix(variable_slice(data, 'national_rainfall_index'), 
            inline=False, sort='descending');
plt.xlabel('Time period');
plt.ylabel('Country');
plt.title('Missing national rainfall index data across countries and time periods \n \n \n \n');

National rainfall index is no longer reported on after 2002.

data = data.loc[~(data.variable=='national_rainfall_index')]

By country¶

Let's look at North America only.

north_america = subregion(data, 'North America')

msno.bar(msno.nullity_sort(time_slice(north_america, '2013-2017'), sort='descending').T, inline=False)
plt.title('Fraction of fields complete by country for North America \n \n');

Question: Is there any pattern in the countries with most missing data?

Question: What are potential reasons for missing data? What can we check?

folium.Map(location=[18.1160128,-77.8364762], tiles="CartoDB positron", 
           zoom_start=5, width=1200, height=600)

Spot check what data is missing for the Bahamas to get more granular understanding.

msno.nullity_filter(country_slice(data, 'Bahamas').T, filter='bottom', p=0.1)

To do: Choose another region to assess for missing data.

By country for a single variable¶

# JSON with coordinates for country boundaries 
geo = r'../data/aquastat/world.json'

null_data = recent['agg_to_gdp'].notnull()*1
map = folium.Map(location=[48, -102], zoom_start=2)
map.choropleth(geo_path=geo, 
               data=null_data,
               columns=['country', 'agg_to_gdp'],
               key_on='feature.properties.name', reset=True,
               fill_color='GnBu', fill_opacity=1, line_opacity=0.2,
               legend_name='Missing agricultural contribution to GDP data 2013-2017')
map

Question: What does the pale pale green mean? Compared to the green? (E.g. Greenland versus Canada)

Now let's functionalize so we can look at other variables geospatially.

def plot_null_map(df, time_period, variable, 
                  legend_name=None):
    geo = r'../data/aquastat/world.json'
    
    
    ts = time_slice(df, time_period).reset_index().copy()
    ts[variable]=ts[variable].notnull()*1
    map = folium.Map(location=[48, -102], zoom_start=2)
    map.choropleth(geo_path=geo, 
                   data=ts,
                   columns=['country', variable],
                   key_on='feature.properties.name', reset=True,
                   fill_color='GnBu', fill_opacity=1, line_opacity=0.2,
                   legend_name=legend_name if legend_name else variable)
    return map

plot_null_map(data, '2013-2017', 'number_undernourished', 'Number undernourished is missing')

Question: Are there any patterns in missing data? Any questions that come to mind for further investigation?

To do: Look at other variables

Over time¶

fig, ax = plt.subplots(figsize=(16, 16));
sns.heatmap(data.groupby(['time_period','variable']).value.count().unstack().T , ax=ax);
plt.xticks(rotation=45);
plt.xlabel('Time period');
plt.ylabel('Variable');
plt.title('Number of countries with data reported for each variable over time');

Profiling¶

Before trying to understand what information is in the data, make sure you understand what the data represents.

Sanity check! Do the values make sense?

Things to do:

Categorical: count, count distinct, assess unique values
Numerical: count, min, max
Spot-check random samples
Slice and dice

Questions to consider:

Are there frequent values that are default values?
Are there fields that represent the same information?
What timestamp should you use?
Are there numerical values reported as strings?
Are there special values?
Are there variables that are numerical but really should be categorical?
Is data consistent across different operating systems, device type, platforms, countries?
Are there any direct relationships between fields (e.g. a value of x always implies a specific value of y)?
What are the units of measurement? Are they consistent?
Is data consistent across the population and time?
Are there obvious changes in reported data around the time of important events that affect data generation (e.g. version release)?

This stage really morphs into the univariate exploration that comes next as you are often diving into each variable one by one and first understanding it, exploring it, then checking that understanding again. We can however do some initial profiling with a few handy python packages.

`pivottablejs`¶

pivottablejs.pivot_ui(time_slice(data, '2013-2017'),)

`pandas_profiling`¶

pandas_profiling.ProfileReport(time_slice(data, '2013-2017'))

To do: Write down observations from profiling¶

To do: Collect list of questions you have coming out of quality assessment and profiling¶

Quality concerns to ask the data source
Questions and hypotheses you want to explore further during exploration
Things you need to understand better and would ask a domain expert

	variable	variable_full
0	total_area	Total area of the country (1000 ha)
576	arable_land	Arable land area (1000 ha)
1152	permanent_crop_area	Permanent crops area (1000 ha)
1728	cultivated_area	Cultivated area (arable land + permanent crops...
2304	percent_cultivated	% of total country area cultivated (%)
2880	total_pop	Total population (1000 inhab)
3456	rural_pop	Rural population (1000 inhab)
4032	urban_pop	Urban population (1000 inhab)
4608	gdp	Gross Domestic Product (GDP) (current US$)
5184	gdp_per_capita	GDP per capita (current US$/inhab)
5760	agg_to_gdp	Agriculture, value added to GDP (%)
6336	human_dev_index	Human Development Index (HDI) [highest = 1] (-)
6912	gender_inequal_index	Gender Inequality Index (GII) [equality = 0; i...
7488	percent_undernourished	Prevalence of undernourishment (3-year average...
8064	number_undernourished	Number of people undernourished (3-year averag...
8640	avg_annual_rain_depth	Long-term average annual precipitation in dept...
9216	avg_annual_rain_vol	Long-term average annual precipitation in volu...
9792	national_rainfall_index	National Rainfall Index (NRI) (mm/year)
10368	surface_water_produced	Surface water produced internally (10^9 m3/year)
10944	groundwater_produced	Groundwater produced internally (10^9 m3/year)
11520	surface_groundwater_overlap	Overlap between surface water and groundwater ...
12096	irwr	Total internal renewable water resources (IRWR...
12672	irwr_per_capita	Total internal renewable water resources per c...
13248	surface_entering	Surface water: entering the country (total) (1...
13824	surface_inflow_submit_no_treaty	Surface water: inflow not submitted to treatie...
14400	surface_inflow_submit_treaty	Surface water: inflow submitted to treaties (1...
14976	surface_inflow_secure_treaty	Surface water: inflow secured through treaties...
15552	total_flow_border_rivers	Surface water: total flow of border rivers (10...
16128	accounted_flow_border_rivers	Surface water: accounted flow of border rivers...
16704	accounted_flow	Surface water: accounted inflow (10^9 m3/year)
17280	surface_to_other_countries	Surface water: leaving the country to other co...
17856	surface_outflow_submit_no_treaty	Surface water: outflow to other countries not ...
18432	surface_outflow_submit_treaty	Surface water: outflow to other countries subm...
19008	surface_outflow_secure_treaty	Surface water: outflow to other countries secu...
19584	surface_total_external_renewable	Surface water: total external renewable (10^9 ...
20160	groundwater_entering	Groundwater: entering the country (total) (10^...
20736	groundwater_accounted_inflow	Groundwater: accounted inflow (10^9 m3/year)
21312	groundwater_to_other_countries	Groundwater: leaving the country to other coun...
21888	groundwater_accounted_outflow	Groundwater: accounted outflow to other countr...
22464	water_total_external_renewable	Water resources: total external renewable (10^...
23040	total_renewable_surface	Total renewable surface water (10^9 m3/year)
23616	total_renewable_groundwater	Total renewable groundwater (10^9 m3/year)
24192	overlap_surface_groundwater	Overlap: between surface water and groundwater...
24768	total_renewable	Total renewable water resources (10^9 m3/year)
25344	dependency_ratio	Dependency ratio (%)
25920	total_renewable_per_capita	Total renewable water resources per capita (m3...
26496	exploitable_regular_renewable_surface	Exploitable: regular renewable surface water (...
27072	exploitable_irregular_renewable_surface	Exploitable: irregular renewable surface water...
27648	exploitable_total_renewable_surface	Exploitable: total renewable surface water (10...
28224	exploitable_regular_renewable_groundwater	Exploitable: regular renewable groundwater (10...
28800	exploitable_total	Total exploitable water resources (10^9 m3/year)
29376	interannual_variability	Interannual variability (WRI) (-)
29952	seasonal_variability	Seasonal variability (WRI) (-)
30528	total_dam_capacity	Total dam capacity (km3)
31104	dam_capacity_per_capita	Dam capacity per capita (m3/inhab)
31680	irrigation_potential	Irrigation potential (1000 ha)
32256	flood_occurence	Flood occurrence (WRI) (-)
32832	total_pop_access_drinking	Total population with access to safe drinking-...
33408	rural_pop_access_drinking	Rural population with access to safe drinking-...
33984	urban_pop_access_drinking	Urban population with access to safe drinking-...

Bahamas	dam_capacity_per_capita	flood_occurence	gender_inequal_index	groundwater_produced	interannual_variability	irrigation_potential	number_undernourished	overlap_surface_groundwater	percent_undernourished	seasonal_variability	surface_groundwater_overlap	surface_water_produced	total_dam_capacity	total_renewable_groundwater	total_renewable_surface
time_period
1958-1962	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
1963-1967	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
1968-1972	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
1973-1977	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
1978-1982	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
1983-1987	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
1988-1992	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
1993-1997	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
1998-2002	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
2003-2007	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
2008-2012	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
2013-2017	NaN	NaN	0.2979	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN

Number of variables	55
Number of observations	199
Total Missing (%)	6.4%
Total size in memory	85.6 KiB
Average record size in memory	440.4 B

Numeric	30
Categorical	0
Date	0
Text (Unique)	1
Rejected	24

Distinct count	113
Unique (%)	58.9%
Missing (%)	3.5%
Missing (n)	7
Infinite (%)	0.0%
Infinite (n)	0

Table of Contents¶

Our plan¶

Data wrangling¶

Data quality assessment and profiling¶

Overview¶

Basic things to do¶

Main questions¶

Helpful packages¶

Example backlog¶

Missing data¶

By variable¶

Deep dive: exploitable variables¶

Deep dive: National rainfall index¶

By country¶

By country for a single variable¶

Over time¶

Profiling¶

`pivottablejs`¶

`pandas_profiling`¶

Overview

Variables

Sample

To do: Write down observations from profiling¶

To do: Collect list of questions you have coming out of quality assessment and profiling¶

Minimum	0
5-th percentile	0
Q1	0
Median	3
Q3	26.065
95-th percentile	270.63
Maximum	2986
Range	2986
Interquartile range	26.065

Standard deviation	250.32
Coef of variation	3.9386
Kurtosis	99.577
Mean	63.557
MAD	93.89
Skewness	9.0735
Sum	12203
Variance	62662
Memory size	1.6 KiB

Value	Count	Frequency (%)
0.0	71	35.7%
3.0	3	1.5%
2.0	3	1.5%
11.0	3	1.5%
80.0	2	1.0%
10.15	2	1.0%
0.3	2	1.0%
1.0	2	1.0%
53.32	1	0.5%
524.7	1	0.5%
Other values (102)	102	51.3%
(Missing)	7	3.5%

Distinct count	41
Unique (%)	21.4%
Missing (%)	3.5%
Missing (n)	7
Infinite (%)	0.0%
Infinite (n)	0

Standard deviation	46.811
Coef of variation	5.6103
Kurtosis	103.31
Mean	8.3438
MAD	14.415
Skewness	9.4653
Sum	1602
Variance	2191.3
Memory size	1.6 KiB

Distinct count	165
Unique (%)	98.8%
Missing (%)	16.1%
Missing (n)	32
Infinite (%)	0.0%
Infinite (n)	0

Minimum	0.0349
5-th percentile	0.62919
Q1	2.8885
Median	8.498
Q3	19.465
95-th percentile	36.168
Maximum	59.23
Range	59.195
Interquartile range	16.576

Standard deviation	11.997
Coef of variation	0.95628
Kurtosis	1.5798
Mean	12.545
MAD	9.5593
Skewness	1.3303
Sum	2095
Variance	143.92
Memory size	1.6 KiB

Distinct count	173
Unique (%)	88.3%
Missing (%)	1.5%
Missing (n)	3
Infinite (%)	0.0%
Infinite (n)	0

Standard deviation	21029
Coef of variation	2.9089
Kurtosis	31.537
Mean	7229.3
MAD	9460.8
Skewness	5.3616
Sum	1416900
Variance	442230000
Memory size	1.6 KiB

Distinct count	174
Unique (%)	96.1%
Missing (%)	9.0%
Missing (n)	18
Infinite (%)	0.0%
Infinite (n)	0

Standard deviation	800.11
Coef of variation	0.68622
Kurtosis	-0.40545
Mean	1166
MAD	662.11
Skewness	0.65639
Sum	211040
Variance	640180
Memory size	1.6 KiB

Distinct count	181
Unique (%)	98.9%
Missing (%)	8.0%
Missing (n)	16
Infinite (%)	0.0%
Infinite (n)	0

Minimum	0.064
5-th percentile	0.86507
Q1	33.095
Median	127.8
Q3	434.5
95-th percentile	3427.4
Maximum	14995
Range	14995
Interquartile range	401.4

Standard deviation	1590.4
Coef of variation	2.6715
Kurtosis	41.211
Mean	595.32
MAD	734.91
Skewness	5.7112
Sum	108940
Variance	2529400
Memory size	1.6 KiB

Distinct count	124
Unique (%)	100.0%
Missing (%)	37.7%
Missing (n)	75
Infinite (%)	0.0%
Infinite (n)	0

Minimum	0.1873
5-th percentile	3.2488
Q1	71.072
Median	328.5
Q3	1205.5
95-th percentile	5561.6
Maximum	36832
Range	36832
Interquartile range	1134.4

Standard deviation	4130.5
Coef of variation	2.6138
Kurtosis	48.996
Mean	1580.3
MAD	1934
Skewness	6.4315
Sum	195950
Variance	17061000
Memory size	1.6 KiB

Distinct count	125
Unique (%)	65.1%
Missing (%)	3.5%
Missing (n)	7
Infinite (%)	0.0%
Infinite (n)	0

Standard deviation	29.869
Coef of variation	1.309
Kurtosis	0.010321
Mean	22.819
MAD	25.123
Skewness	1.1509
Sum	4381.2
Variance	892.16
Memory size	1.6 KiB

Table of Contents¶

Our plan¶

Data wrangling¶

Data quality assessment and profiling¶

Overview¶

Basic things to do¶

Main questions¶

Helpful packages¶

Example backlog¶

Missing data¶

By variable¶

Deep dive: exploitable variables¶

Deep dive: National rainfall index¶

By country¶

By country for a single variable¶

Over time¶

Profiling¶

pivottablejs¶

pandas_profiling¶

Overview

Variables

Sample

To do: Write down observations from profiling¶

To do: Collect list of questions you have coming out of quality assessment and profiling¶

`pivottablejs`¶

`pandas_profiling`¶

Distinct count	41
Unique (%)	23.3%
Missing (%)	11.6%
Missing (n)	23
Infinite (%)	0.0%
Infinite (n)	0

Minimum	0
5-th percentile	0.35
Q1	2.3
Median	2.9
Q3	3.325
95-th percentile	3.825
Maximum	4.9
Range	4.9
Interquartile range	1.025

Standard deviation	0.99495
Coef of variation	0.36912
Kurtosis	0.96957
Mean	2.6955
MAD	0.75212
Skewness	-1.0132
Sum	474.4
Variance	0.98992
Memory size	1.6 KiB

Distinct count	185
Unique (%)	98.4%
Missing (%)	5.5%
Missing (n)	11
Infinite (%)	0.0%
Infinite (n)	0

Mean	384890000000
Minimum	37860000
Maximum	17900000000000
Zeros (%)	0.0%

Standard deviation	1604200000000
Coef of variation	4.1681
Kurtosis	85.899
Mean	384890000000
MAD	549640000000
Skewness	8.7022
Sum	72359000000000
Variance	2.5736e+24
Memory size	1.6 KiB

Minimum	276
5-th percentile	537.11
Q1	1732.2
Median	4911.5
Q3	14474
95-th percentile	50644
Maximum	101910
Range	101640
Interquartile range	12742

Standard deviation	17543
Coef of variation	1.4
Kurtosis	5.4979
Mean	12531
MAD	12391
Skewness	2.2522
Sum	2355700
Variance	307760000
Memory size	1.6 KiB

Distinct count	155
Unique (%)	99.4%
Missing (%)	21.6%
Missing (n)	43
Infinite (%)	0.0%
Infinite (n)	0

Minimum	0.0164
5-th percentile	0.066375
Q1	0.18738
Median	0.38605
Q3	0.52572
95-th percentile	0.65758
Maximum	0.744
Range	0.7276
Interquartile range	0.33835